"""
	"name" varchar(60) NULL,
	create_time timestamp NULL,
	"sequence" int4 NULL,
	score numeric(10,2) NULL,
	remarks text NULL,
"""

from import_sql_exec import DB


def sql_column(column_name: str, column_type: str, nullAble: str = True):
    null = "NULL" if nullAble else "NOT NULL"
    return f"{column_name} {column_type} {null}"


def sql_create_table(table_name: str, columns: str, comments: str, exe=False):
    sql = f"""CREATE TABLE {table_name} (
uuid varchar(36) NOT NULL,
{columns},
data_time timestamp NOT NULL,
data_user varchar(50) NOT NULL,
data_comment text NULL,
CONSTRAINT {table_name}_pk PRIMARY KEY (uuid)
)
WITH (
    OIDS=FALSE
) ;
          """
    if exe:
        db = DB()
        db.exec(sql)
        for comment in comments.split(";"):
            db.exec(comment.strip())
        db.commit()
        db.close()
    return f"""{sql}
{comments};
"""


def drop_table(table_name: str, exe=False):
    sql = f"drop table {table_name};"
    if exe:
        try:
            db = DB()
            db.exec(sql)
            db.commit()
            db.close()
        except:
            pass
    return sql


def select_all(table_name: str):
    return f"select * from {table_name};"


def comment_on(table_name: str, column: str, comment: str):
    return f"COMMENT ON COLUMN {table_name}.{column} IS '{comment}'"


def create_table(
    table_name: str, columns: str, types: str, comments: str, split=",", exe=False
):
    _columns = columns.rstrip().lower().split(split)
    _types = types.rstrip().lower().split(split)
    _comments = comments.rstrip().lower().split(split)
    _columns_join = []
    _comments_join = []
    for c, t, r in zip(_columns, _types, _comments):
        column = "_".join(c.strip().split(" "))
        _columns_join.append(sql_column(column, t))
        _comments_join.append(comment_on(table_name, column, r))
    return sql_create_table(
        table_name, ",\n".join(_columns_join), ";\n".join(_comments_join), exe
    )
